<?php
final class DB_MySQL implements DB_IAdvanced,IConfig_Get
{
      private static $__Regexp_PagingLimitValue='/limit (\?|\d+\,*\w*|\w+\,\w+)\,(\d+)$/';

      public static function BindRWI($str)
      {                
            return '`'.$str.'`';
      }

    public static function GetRowByValue($tableName,$fieldName,$value,&$dbh)
    {
        $data=NULL;
        {            
            $sth=$dbh->Prepare('select * from '.$tableName.' where '.$fieldName.'=? limit 0,1');
            {
                $params=array($value);
                EPDO::BindSTMT($sth,$params);
                $sth->Execute();
                $data=$sth->FetchAll();
                if(count($data)==0){
                    $data=NULL;
                }
                else{
                    $data=$data[0];
                }

            }
            $sth=NULL;
        }
        return $data;
    }


      public static function GetRowByID($tableName,$primary,&$dbh)
      {
            self::__ShortcutParams($tableName, $primary);
            $params=self::__PreparePrimary($primary);           
            return $dbh->Query('select * from '.self::BindRWI($tableName).' where '.self::BindRWI($params['FieldName']).'='.$params['Value'])->Fetch();
      }

      /**
       * Start UnStandard
       */
  	  public static function GetRowByIDForFields($tableName,$primary,$field,&$dbh)
      {
            self::__ShortcutParams($tableName, $primary);
            $params=self::__PreparePrimary($primary);           
            return $dbh->Query('select '.$field.' from '.self::BindRWI($tableName).' where '.self::BindRWI($params['FieldName']).'='.$params['Value'])->Fetch();
      }
      /**
       * End UnStandard
       */
      
      public static function GetValueByID($tableName,$primary,$field,&$dbh)
      {
            self::__ShortcutParams($tableName, $primary);
            $params=self::__PreparePrimary($primary);
            $row=$dbh->Query('select '.$field.' from '.self::BindRWI($tableName).' where '.self::BindRWI($params['FieldName']).'='.$params['Value'])->Fetch();
            $value=$row[$field];
            return $value==NULL ? 'NULL':$value;
      }
	
      public static function DeleteRowByID($tableName,$primary,&$dbh)
      {
            self::__ShortcutParams($tableName, $primary);
            $params=self::__PreparePrimary($primary);
            return $dbh->Exec('delete from '.self::BindRWI($tableName).' where '.self::BindRWI($params['FieldName']).'='.$params['Value']);
      }
      
      public static function UpdateRowByID($tableName,$primary,$data,&$dbh)
      {
            self::__ShortcutParams($tableName, $primary);
            if(gettype($data)!='array') throw new Exception('Class.DB_MYSQL.UpdateRowByID：第三个参数的类型不正确必须是关联数组。');

            $params=self::__PreparePrimary($primary);
            $fields=array_keys($data);
            $sql='update '.$tableName.' ';           
            {
                  $sql_filed='set ';
                  foreach($fields as $filed)
                  {
                        $sql_filed.="$filed=:$filed,";
                  }
                  $sql_filed=substr($sql_filed,0,strlen($sql_filed)-1);
            }
            $sql.=($sql_filed.' where '.$params['FieldName'].'='.$params['Value']);
            
            $sth=$dbh->Prepare($sql);
            {
                  EPDO::BindSTMT($sth,$data);
            }
            return $sth->Execute();
      }

      public static function UpdateRowByField($tableName,$fieldNameAndValue,$data,&$dbh)
      {
            self::__ShortcutParams($tableName,$fieldNameAndValue);
            if(gettype($data)!='array') throw new Exception('Class.DB_MYSQL.UpdateRowByID：第三个参数的类型不正确必须是关联数组。');

            $params=array();
            {
                $tmp=explode(':',$fieldNameAndValue);
                $params['FieldName']=$tmp[0];
                $params['Value']=$tmp[1];
            }

            $fields=array_keys($data);
            $sql='update '.$tableName.' ';
            {
                  $sql_filed='set ';
                  foreach($fields as $filed)
                  {
                        $sql_filed.="$filed=:$filed,";
                  }
                  $sql_filed=substr($sql_filed,0,strlen($sql_filed)-1);
            }
            $sql.=($sql_filed.' where '.$params['FieldName'].'=:'.$params['FieldName']);
            $data[$params['FieldName']]=$params['Value'];
            
            $sth=$dbh->Prepare($sql);
            {
                  EPDO::BindSTMT($sth,$data);
            }
            return $sth->Execute();
      }

      public static function GetData($sql,&$dbh)
      {
            self::TestParams($sql);
            $data=array();
            {                  
                  $data['Meta']=self::__GetMetadata($dbh,$sql);
                  $data['Record']=$dbh->Query(preg_replace(self::$__Regexp_PagingLimitValue,' limit '.$data['Meta']['Limit_Start'].','.$data['Meta']['PageSize'],$sql))->FetchAll();
            }
            return $data;
      }

      public static function PGetData($sql,$params,&$dbh)
      {
            self::TestParams($sql,$params);
            $data=array();
            {
                  $data['Meta']=self::__GetMetadata($dbh,$sql,$params);
                  $sth=$dbh->Prepare(preg_replace(self::$__Regexp_PagingLimitValue,' limit '.$data['Meta']['Limit_Start'].','.$data['Meta']['PageSize'],$sql));
                  {
                        EPDO::BindSTMT($sth,$params);
                        $sth->Execute();
                        $data['Record']=$sth->FetchAll();
                  }
                  $sth=NULL;
            }
            return $data;
      }

      private static function __ShortcutParams($tableName,$primary)
      {
            if(gettype($tableName)!='string') throw new Exception('Class.DB_MYSQL.ShortcutParams：第一个参数的类型不正确必须是字符串。');
            if(gettype($primary)!='string'&&!is_numeric($primary)) throw new Exception('Class.DB_MYSQL.ShortcutParams：第二个参数的类型不正确,必须是字符串或数字中的一种。');
      }

      private static function __PreparePrimary($primary)
      {
            if(is_numeric($primary))
            {
                  return array('FieldName'=>'ID','Value'=>$primary);
            }
            else if(is_string($primary))
            {
                  $params=explode(':',$primary);
                  if(!is_numeric($params[1])) throw new Exception('Class.DB_MYSQL.PreparePrimary：主键值必须是数字。'.$params[1]);
                  return array('FieldName'=>$params[0],'Value'=>$params[1]);
            }
            else{
                throw new Exception('Class.DB_Advanced.PreparePrimary：不正确的主键值或格式。只能是数字或组合格式（主键名称：主键值）。');
            }
      }

      private static function TestParams($sql,$params=NULL)
      {            
            if(!preg_match(self::$__Regexp_PagingLimitValue,$sql)){
                  throw new Exception('Class.DB_MYSQL.TestParams：SQL语句的末尾必须包含分页limit语句。格式：limit 页码问号通配符,显示数量|limit 页码值,显示数量|limit 页码值,页码参数名,显示数量|limit 页码参数名,页码值获取方法,显示数量。SQL语句：'.$sql.'。');
            }

            if($params)
            {
                  //参数类型不对
                  if(gettype($params)!='array'){
                        throw new Exception('Class.DB_MYSQL.TestParams：$params参数类型不对，应该以索引数组的形式提供。');
                  }

                  //参数数量不对
                  $paramsCount=count($params);
                  if(!$paramsCount){
                        throw new Exception('Class.DB_MYSQL.TestParams：$params参数数量为0。');
                  }

                  //SQL语句的参数
                  $sqlParamsCount=substr_count(preg_replace(self::$__Regexp_PagingLimitValue,'',$sql),'?');
                  if(!$sqlParamsCount){
                        throw new Exception('Class.DB_MYSQL.TestParams：SQL语句用?标识出的参数为0。');
                  }

                  //SQL语句的参数数量与实际给出参数数量不符
                  if($paramsCount!=$sqlParamsCount){
                        throw new Exception('Class.DB_MYSQL.TestParams：SQL语句标识出的参数数量与实际给出的参数数量不一致。');
                  }
            }
      }

	private static function __GetMetadata(&$dbh,$sql,$params=NULL)
	{
            $currentPage=NULL;
            $pageSize=NULL;            
            $limitStrArray=array();
            {
                  if(preg_match_all(self::$__Regexp_PagingLimitValue,$sql,$limitStrArray))
                  {
                        $currentPage=$limitStrArray[1][count($limitStrArray[1])-1];
                        $pageSize=$limitStrArray[2][count($limitStrArray[1])-1];
                  }
                  else{
                        throw new Exception('Class.DB_MYSQL.__GetMetaData：错误的元数据模式。格式：limt ?|值|值,参数名|参数名,方法。SQL:'.$sql.'。');
                  }
            }

		$rowCount=self::__RowCount($dbh,$sql,$params);
		$pageCount=$rowCount/$pageSize;
            {
                  if($pageCount<1){
                        $pageCount=1;
                  }
                  else {
                        $pageCount=ceil($pageCount);
                  }
            }

            $paramName='page';
            $paramMethod='get';
            {
                  if($currentPage=='?'||preg_match('/^\d+$/',$currentPage))
                  {
                        if($temp=self::GetConfig())
                        {
                              $paramName=$temp['ParamName'];
                              $paramMethod=$temp['Method'];
                        }
                        if($currentPage=='?'){
                              $currentPage=self::__GetMetadata_ParamValue($paramName,$paramMethod);
                        }
                        else{
                              $currentPage=(int)$currentPage;
                        }      
                  }
                  else
                  {                        
                        if(preg_match('/^\d+\,\w+$/',$currentPage))
                        {
                              $temp=split(',',$currentPage);
                              $currentPage=(int)$temp[0];
                              $paramName=$temp[1];
                              if($temp=self::GetConfig()){
                                    $paramMethod=$temp['Method'];
                              }
                        }
                        else
                        {
                              $temp=split(',',$currentPage);
                              $paramName=$temp[0];
                              $paramMethod=$temp[1];
                              $currentPage=self::__GetMetadata_ParamValue($paramName,$paramMethod);
                        }                                               
                  }
                  if($currentPage<1) {
                        $currentPage=1;
                  }
                  if($currentPage>$pageCount) {
                        $currentPage=$pageCount;
                  }
            }
            
		$limit_start=(($currentPage-1)*$pageSize);

            return array('ParamName'=>$paramName,'ParamMethod'=>$paramMethod,'CurrentPage'=>$currentPage,'PageSize'=>$pageSize,'RowCount'=>$rowCount,'PageCount'=>$pageCount,'Limit_Start'=>$limit_start);
	}

      private static function __RowCount(&$dbh,$sql,$params=NULL)
	{
            $rowCount=NULL;
		$sqlCount='select count(*) ';
		$sqlCount.=substr($sql,strpos($sql,'from'),strlen($sql));
            $sqlCount=preg_replace(self::$__Regexp_PagingLimitValue,'',$sqlCount);
           
            if($params)
            {
                  $stmt=$dbh->Prepare($sqlCount);
                  {
                        EPDO::BindSTMT($stmt,$params);
                        $stmt->Execute();
                        $result=$stmt->Fetch();
                        $rowCount=$result['count(*)'];
                  }
                  $stmt=NULL;
            }
            else
            {
                  $stmt=$dbh->Query($sqlCount);
                  {
                        $result=$stmt->Fetch();
                        $rowCount=$result['count(*)'];
                  }                  
                  $stmt=NULL;
            }
		return $rowCount;
	}

      private static function __GetMetadata_ParamValue($name,$method)
      {
            switch (strtolower($method))
            {
                  case 'get':
                        return Functions::UrlArgs($name);
                  break;
                  case 'post':
                        return Functions::FormArgs($name);
                  break;
                  default:
                        throw new Exception('Class.DB_MYSQL.__GetMetaData：您所用的格式：limt 字符串,字符串。但该方法不支持获取参数的类型'.$method.'。SQL:'.$sql);
                  break;
            }
      }

      public static function GetConfig()
      {
            if(class_exists('Config'))
            {
                  if(method_exists('Config','Paging'))
                  {
                        $paging=Config::Paging();
                        if(gettype($paging)!='array') $paging=NULL;
                        return $paging;
                  }
            }
      }
}
?>